import pymysql
from pymysql import Error
import logging
from datetime import datetime
import re
from typing import List, Dict, Optional, Any

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger('database_manager')

# 数据库字段映射
DB_FIELD_MAPPING = {
    '标题': 'title',
    '价格(元/月)': 'price',
    '户型': 'room_type',
    '面积(平米)': 'area',
    '楼层': 'floor_info',
    '总楼层': 'total_floors',
    '地址': 'address',
    '标签': 'tags',
    '经纪人': 'agent',
    '特色': 'features',
    '链接': 'link',
    '爬取时间': 'crawl_time',
    '房屋编码': 'house_code',
    '付款方式': 'payment_method',
    '详细户型': 'detailed_layout',
    '朝向': 'orientation',
    '装修': 'decoration',
    '配套设施': 'facilities',
    '房源描述': 'description',
    '出租要求': 'rental_requirements',
    'city': 'city',
    'district': 'district',
    'source_type': 'source_type'
}

class DatabaseManager:
    """数据库管理类 - 提供安全、高效的数据库操作"""

    def __init__(self, config):
        self.config = config.copy()  # 创建配置副本，避免修改原配置
        self.connection = None
        self.reconnect_attempts = 0
        self.max_reconnect_attempts = 3
        self.connect()
        
        # 初始化时检查表是否存在
        self._ensure_table_exists()

    def connect(self):
        """连接数据库"""
        try:
            # 添加连接池相关参数
            connect_params = {
                'cursorclass': pymysql.cursors.DictCursor,
                'charset': 'utf8mb4',
                'autocommit': True
            }
            
            # 更新配置，使用DictCursor以便返回字典格式结果
            self.config.update(connect_params)
            
            self.connection = pymysql.connect(**self.config)
            self.reconnect_attempts = 0  # 重置重连计数
            logger.info(f"数据库连接成功: {self.config.get('host')}:{self.config.get('port')}/{self.config.get('database')}")
            return True
        except Error as e:
            logger.error(f"数据库连接失败: {e}")
            self.connection = None
            return False

    def reconnect(self):
        """重连数据库"""
        if self.connection:
            try:
                self.connection.close()
            except Exception as e:
                logger.error(f"关闭数据库连接失败: {e}")
        
        # 增加重连尝试次数限制
        if self.reconnect_attempts < self.max_reconnect_attempts:
            self.reconnect_attempts += 1
            logger.info(f"尝试第 {self.reconnect_attempts} 次重连数据库...")
            return self.connect()
        else:
            logger.error(f"已达到最大重连次数 ({self.max_reconnect_attempts})，放弃重连")
            return False

    def commit(self):
        """提交当前事务"""
        if self.connection:
            try:
                self.connection.commit()
                logger.debug("事务提交成功")
                return True
            except Error as e:
                logger.error(f"提交事务失败: {e}")
                return False
        return False

    def rollback(self):
        """回滚当前事务"""
        if self.connection:
            try:
                self.connection.rollback()
                logger.debug("事务回滚成功")
                return True
            except Error as e:
                logger.error(f"回滚事务失败: {e}")
                return False
        return False

    def close(self):
        """安全关闭数据库连接"""
        if self.connection:
            try:
                self.connection.close()
                logger.info("数据库连接已安全关闭")
                self.connection = None
            except Exception as e:
                logger.error(f"关闭数据库连接时出错: {e}")



    def execute_query(self, query: str, params: Optional[tuple] = None, fetch: bool = False) -> Any:
        """
        执行SQL查询，带错误处理和自动重连
        
        Args:
            query: SQL查询语句
            params: 查询参数
            fetch: 是否返回查询结果
            
        Returns:
            查询结果或布尔值（操作是否成功）
        """
        if not self.connection:
            logger.error("数据库未连接，尝试重连...")
            if not self.reconnect():
                return None

        try:
            with self.connection.cursor() as cursor:
                # 记录执行的SQL（不记录参数值，避免安全风险）
                logger.debug(f"执行SQL: {query[:200]}...")
                cursor.execute(query, params)
                
                if fetch:
                    result = cursor.fetchall()
                    logger.debug(f"查询返回 {len(result)} 条记录")
                    return result
                
                self.connection.commit()
                logger.debug(f"SQL执行成功，影响行数: {cursor.rowcount}")
                return True
        except Error as e:
            error_msg = str(e)
            logger.error(f"SQL执行错误: {error_msg}")
            
            # 处理常见的错误情况
            if "MySQL server has gone away" in error_msg or "Lost connection to MySQL server" in error_msg:
                logger.warning("数据库连接丢失，尝试重连...")
                if self.reconnect():
                    return self.execute_query(query, params, fetch)
            elif "Duplicate entry" in error_msg:
                logger.warning(f"数据重复: {error_msg}")
                # 对于重复数据，返回True表示已经处理过
                return True
            
            return None
        except Exception as e:
            logger.error(f"未知错误: {e}", exc_info=True)
            return None

    def _ensure_table_exists(self):
        """确保数据表存在，如果不存在则创建"""
        create_table_sql = """
        CREATE TABLE IF NOT EXISTS house_info (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            price DECIMAL(10,2) DEFAULT 0,
            room_type VARCHAR(50),
            area DECIMAL(10,2) DEFAULT 0,
            floor_info VARCHAR(50),
            total_floors INT DEFAULT 0,
            address VARCHAR(255),
            tags TEXT,
            agent VARCHAR(100),
            features TEXT,
            link VARCHAR(255) UNIQUE,
            crawl_time DATETIME,
            house_code VARCHAR(100),
            payment_method VARCHAR(50),
            detailed_layout VARCHAR(50),
            orientation VARCHAR(20),
            decoration VARCHAR(20),
            facilities TEXT,
            description TEXT,
            rental_requirements TEXT,
            city VARCHAR(50),
            district VARCHAR(50),
            source_type VARCHAR(50),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """
        
        try:
            result = self.execute_query(create_table_sql)
            if result:
                logger.info("数据表检查/创建成功")
        except Exception as e:
            logger.error(f"创建数据表失败: {e}")
    
    def insert_house(self, house_data: Dict[str, Any], city: Optional[str] = None, 
                    district: Optional[str] = None, source_type: Optional[str] = None) -> bool:
        """
        插入或更新房源数据，带数据清洗和验证
        
        Args:
            house_data: 房源数据字典
            city: 城市
            district: 区域
            source_type: 房源来源类型
            
        Returns:
            是否成功插入/更新
        """
        if not self.connection:
            logger.error("数据库未连接，无法插入数据")
            return False

        # 转换字段名
        db_data = {}
        for chinese_key, english_key in DB_FIELD_MAPPING.items():
            if chinese_key in house_data:
                db_data[english_key] = house_data[chinese_key]
            # 也检查英文键
            elif english_key in house_data:
                db_data[english_key] = house_data[english_key]

        # 添加额外字段
        if city:
            db_data['city'] = city
        if district:
            db_data['district'] = district
        if source_type:
            db_data['source_type'] = source_type

        # 数据清洗和验证
        self._clean_house_data(db_data)

        # 检查必要字段
        if not db_data.get('title') or not db_data.get('link'):
            logger.warning("缺少必要字段(title或link)，跳过数据插入")
            return False

        # 构建插入SQL
        columns = []
        placeholders = []
        values = []

        for key, value in db_data.items():
            if value is not None:
                columns.append(key)
                placeholders.append('%s')
                values.append(value)

        if not columns:
            logger.warning("没有有效数据可插入")
            return False

        columns_str = ', '.join(columns)
        placeholders_str = ', '.join(placeholders)

        # 使用ON DUPLICATE KEY UPDATE进行更新，避免重复数据
        update_fields = [f'{col}=VALUES({col})' for col in columns if col != 'link' and col != 'id']
        query = f"""
        INSERT INTO house_info ({columns_str}) 
        VALUES ({placeholders_str})
        ON DUPLICATE KEY UPDATE 
            {', '.join(update_fields)}, 
            updated_at = CURRENT_TIMESTAMP
        """

        result = self.execute_query(query, tuple(values))
        if result:
            logger.debug(f"成功插入/更新房源数据: {db_data.get('title', '未知标题')}")
            return True
        else:
            logger.error(f"插入房源数据失败: {db_data.get('title')}")
            return False
    
    def _clean_house_data(self, data: Dict[str, Any]) -> None:
        """清洗和规范化房源数据"""
        # 处理爬取时间格式
        if 'crawl_time' in data:
            if isinstance(data['crawl_time'], str):
                try:
                    # 尝试不同的时间格式
                    for fmt in ['%Y-%m-%d %H:%M:%S', '%Y-%m-%d %H:%M', '%Y-%m-%d', '%Y/%m/%d']:
                        try:
                            data['crawl_time'] = datetime.strptime(data['crawl_time'], fmt)
                            break
                        except ValueError:
                            continue
                    else:
                        data['crawl_time'] = datetime.now()
                except Exception:
                    data['crawl_time'] = datetime.now()
            elif not isinstance(data['crawl_time'], datetime):
                data['crawl_time'] = datetime.now()
        else:
            data['crawl_time'] = datetime.now()

        # 处理价格类型
        if 'price' in data:
            if isinstance(data['price'], str):
                # 提取数字，处理各种价格格式
                cleaned_price = re.sub(r'[^\d.]', '', data['price'])
                try:
                    data['price'] = float(cleaned_price) if cleaned_price else 0.0
                except ValueError:
                    data['price'] = 0.0
            elif not isinstance(data['price'], (int, float)):
                data['price'] = 0.0
        
        # 处理面积
        if 'area' in data:
            if isinstance(data['area'], str):
                cleaned_area = re.sub(r'[^\d.]', '', data['area'])
                try:
                    data['area'] = float(cleaned_area) if cleaned_area else 0.0
                except ValueError:
                    data['area'] = 0.0
        
        # 处理总楼层
        if 'total_floors' in data:
            if isinstance(data['total_floors'], str):
                cleaned_floors = re.sub(r'[^\d]', '', data['total_floors'])
                try:
                    data['total_floors'] = int(cleaned_floors) if cleaned_floors else 0
                except ValueError:
                    data['total_floors'] = 0
        
        # 截断过长的文本字段
        text_fields = ['title', 'address', 'agent', 'tags', 'features', 'link']
        for field in text_fields:
            if field in data and isinstance(data[field], str):
                max_length = 255
                if len(data[field]) > max_length:
                    data[field] = data[field][:max_length]

    def insert_batch_houses(self, houses_data: List[Dict[str, Any]], city: Optional[str] = None,
                            district: Optional[str] = None, source_type: Optional[str] = None) -> int:
        """
        批量插入房源数据，使用事务确保数据完整性
        """
        if not self.connection:
            logger.error("数据库未连接，无法插入数据")
            return 0

        if not houses_data:
            logger.warning("没有数据需要插入")
            return 0

        success_count = 0
        total_count = len(houses_data)
        batch_size = 50

        logger.info(f"开始批量插入数据: 共 {total_count} 条记录")

        try:
            # 开始事务
            self.connection.begin()

            # 分批处理
            for i in range(0, total_count, batch_size):
                batch = houses_data[i:i + batch_size]
                batch_success = 0

                for house_data in batch:
                    if self.insert_house(house_data, city, district, source_type):
                        batch_success += 1

                success_count += batch_success
                logger.debug(f"批次 {i // batch_size + 1} 完成: 成功 {batch_success}/{len(batch)} 条")

                # 每处理完一批就提交一次，避免长时间占用事务
                self.connection.commit()
                # 立即开始新的事务
                if i + batch_size < total_count:
                    self.connection.begin()

            logger.info(f"批量插入完成: 成功 {success_count}/{total_count}")
            return success_count

        except Exception as e:
            # 发生错误时回滚事务
            logger.error(f"批量插入失败，回滚事务: {e}")
            try:
                self.connection.rollback()
            except:
                pass
            return 0
    
    def get_house_count(self, conditions: Optional[Dict[str, Any]] = None) -> int:
        """
        获取房源数量
        
        Args:
            conditions: 查询条件
            
        Returns:
            房源数量
        """
        query = "SELECT COUNT(*) as count FROM house_info"
        params = []
        
        if conditions:
            where_clauses = []
            for key, value in conditions.items():
                if value is not None:
                    where_clauses.append(f"{key} = %s")
                    params.append(value)
            
            if where_clauses:
                query += " WHERE " + " AND ".join(where_clauses)
        
        result = self.execute_query(query, tuple(params), fetch=True)
        if result and len(result) > 0:
            return result[0]['count'] if isinstance(result[0], dict) else result[0][0]
        return 0
    
    def get_recent_houses(self, limit: int = 10, city: Optional[str] = None) -> List[Dict[str, Any]]:
        """
        获取最近的房源数据
        
        Args:
            limit: 返回记录数限制
            city: 可选的城市筛选
            
        Returns:
            房源数据列表
        """
        query = "SELECT * FROM house_info"
        params = []
        
        if city:
            query += " WHERE city = %s"
            params.append(city)
        
        query += " ORDER BY crawl_time DESC LIMIT %s"
        params.append(limit)
        
        result = self.execute_query(query, tuple(params), fetch=True)
        return result or []

    def close(self):
        """安全关闭数据库连接"""
        if self.connection:
            try:
                self.connection.close()
                logger.info("数据库连接已安全关闭")
                self.connection = None
            except Exception as e:
                logger.error(f"关闭数据库连接时出错: {e}")